package restaurant.selector.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import restaurant.selector.Restaurant;
import restaurant.selector.User;

public class JdbcWorker implements DatabaseWorker {

	private Connection connection;
	
	public JdbcWorker( Connection connection ){
		
		this.connection = connection;
	}
	
	@Override
	public Restaurant getRestaurantById(int id) {
		try{
			PreparedStatement prepStmt = connection.prepareStatement("SELECT * FROM restaurant WHERE id = ?");
			prepStmt.setInt(1, id);
			ResultSet resultSet = prepStmt.executeQuery();
			if(resultSet.next() )
				return new Restaurant(resultSet.getInt("id"), resultSet.getInt("weather"),
						resultSet.getString("name"), resultSet.getDate("startDate"),
						resultSet.getDate("endDate"), resultSet.getDate("startTime"),
						resultSet.getDate("endTime"), resultSet.getInt("distance"),
						resultSet.getBoolean("quick") );
			
		}catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}

	@Override
	public Restaurant getRestaurantByName(String name) {

		try{
			PreparedStatement prepStmt = connection.prepareStatement("SELECT * FROM restaurant WHERE name = ?");
			prepStmt.setString(1, name);
			ResultSet resultSet = prepStmt.executeQuery();
			if(resultSet.next() )
				return new Restaurant(resultSet.getInt("id"), resultSet.getInt("weather"),
						resultSet.getString("name"), resultSet.getDate("startDate"),
						resultSet.getDate("endDate"), resultSet.getDate("startTime"),
						resultSet.getDate("endTime"), resultSet.getInt("distance"),
						resultSet.getBoolean("quick") );
			
		}catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}

	@Override
	public List<Restaurant> getRestaurants() {
		List<Restaurant> restaurants = new ArrayList<Restaurant>();
		 try {
				PreparedStatement prepStmt = connection.prepareStatement("SELECT * FROM restaurant");
				ResultSet resultSet = prepStmt.executeQuery();
				while (resultSet.next()) {
					
					restaurants.add( new Restaurant(resultSet.getInt("id"), resultSet.getInt("weather"),
							resultSet.getString("name"), resultSet.getDate("startDate"),
							resultSet.getDate("endDate"), resultSet.getDate("startTime"),
							resultSet.getDate("endTime"), resultSet.getInt("distance"),
							resultSet.getBoolean("quick") ));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		return restaurants;
	}

	@Override
	public User getUserById(int id) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public User getUserByName(String name) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<User> getUsers() {
		List<User> restaurants = new ArrayList<User>();
		 try {
				PreparedStatement prepStmt = connection.prepareStatement("SELECT * FROM user");
				ResultSet resultSet = prepStmt.executeQuery();
				while (resultSet.next()) {
					
					restaurants.add( new User(resultSet.getInt("id"),
							resultSet.getString("name")));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		return restaurants;
	}

	@Override
	public void addRestaurant(String name, boolean quick, int distance) {

		try{
			PreparedStatement st = connection.prepareStatement("INSERT INTO restaurant (name, quick, distance, weather) VALUES (?,?,?,15)");
			st.setString(1, name);
			st.setBoolean(2, quick);
			st.setInt(3, distance);

			System.out.println( st );
			
			st.execute();
		}catch(SQLException e){
			e.printStackTrace();
		}
	}

	@Override
	public void addUser(String name) {
		try{
			PreparedStatement st = connection.prepareStatement("INSERT INTO user (name) VALUES (?)");
			st.setString(1, name);
			st.execute();
		}catch(SQLException e){
			e.printStackTrace();
		}
	}

	@Override
	public void deleteRestaurant(Restaurant r) {
		try{
			PreparedStatement st = connection.prepareStatement("DELETE FROM restaurant WHERE id = ?");
			st.setInt(1, r.getId());
			st.execute();
		}catch(SQLException e){
			e.printStackTrace();
		}
	}

	@Override
	public void deleteUser(User u) {
		try{
			PreparedStatement st = connection.prepareStatement("DELETE FROM user WHERE id = ?");
			st.setInt(1, u.getId());
			st.execute();
		}catch(SQLException e){
			e.printStackTrace();
		}		
	}

}
